﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
using Ortodoctor.Componentes;
using System.Web;
using System.Web.Services;

namespace Ortodoctor.modelo
{
    public class modeloParcelas : conexao
    {
        public modeloParcelas()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public void insert(Parcelas p)
        {
            try
            {
                //con.open();
                String sql = "INSERT INTO parcelas(valor, data_vencimento, id_procedimento_cliente, " +
                             "status) VALUES (:p1, :p2, :p3, :p4)";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);

                com.Parameters.AddWithValue("p1", p.Valor);
                com.Parameters.AddWithValue("p2", p.DataVencimento.ToString("MM/dd/yyyy"));
                com.Parameters.AddWithValue("p3", p.ProcedimentoCliente);
                com.Parameters.AddWithValue("p4", p.Status);
                com.ExecuteNonQuery();
            }
            finally { conn.Close(); }
        }

        public bool insertWithTransaction(Parcelas p)
        {
            String sql = string.Format(@"INSERT INTO parcelas(valor, data_vencimento, 
id_procedimento_cliente, status) VALUES ({0}, '{1}', {2}, '{3}')", p.Valor.ToString().Replace(",", "."), p.DataVencimento.ToString("MM/dd/yyyy"),
                                                                 p.ProcedimentoCliente, p.Status);
            return conexao.executeTransactionNonQuery(sql);
        }

        public void transferirParaNovoMes(int id_parcela)
        {
            try
            {
                //con.open();
                String sql = "UPDATE parcelas SET data_vencimento=select cast " +
                   " ((data_vencimento + 1*'1 month'::interval) as date) from parcelas, id_usuario_modificacao=:id_mod  where id_parceoa = :id " +
                   "and data_vencimento=(select max(data_vencimento) from parcelas where id_parcela = :id)";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id_parcela);
                com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable getAll()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = @"SELECT * FROM PARCELAS INNER JOIN PROCEDIMENTO_CLIENTE USING(ID_PROCEDIMENTO_CLIENTE) 
                         INNER JOIN PROCEDIMENTO USING(ID_PROCEDIMENTO) INNER JOIN CLIENTE USING(ID_CLIENTE) ORDER BY id_parcela, NOME, DATA_VENCIMENTO";
                if (HttpContext.Current.Session["USUARIO_ACESSO"].ToString().CompareTo("2") != 0)
                {
                    sql = @"SELECT * FROM PARCELAS INNER JOIN PROCEDIMENTO_CLIENTE USING(ID_PROCEDIMENTO_CLIENTE) 
                         INNER JOIN PROCEDIMENTO USING(ID_PROCEDIMENTO) INNER JOIN CLIENTE USING(ID_CLIENTE) 
                         WHERE CLIENTE.ID_CLINICA=" + HttpContext.Current.Session["ID_CLINICA"] + " ORDER BY id_parcela, NOME, DATA_VENCIMENTO";
                }
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getOrcamento(int id_orcamento)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM parcelas INNER JOIN procedimento_cliente USING(id_procedimento_cliente) " +
                             "INNER JOIN procedimento USING(id_procedimento) WHERE id_orcamento=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id_orcamento", id_orcamento);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getOrcamentoPorCliente(int id_cliente)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM parcelas INNER JOIN procedimento_cliente USING(id_procedimento_cliente) " +
                             "INNER JOIN procedimento USING(id_procedimento) WHERE id_cliente=:id";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id_cliente", id_cliente);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public void update(Parcelas p)
        {
            try
            {
                //con.open();
                String sql = "UPDATE parcelas SET valor=:p1, id_usuario_modificacao=:id_mod " +
                    "WHERE id_procedimento_cliente=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", p.Valor);
                //com.Parameters.AddWithValue("p2", p.Status);

                com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.Parameters.AddWithValue("id", p.ProcedimentoCliente);
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void updateStatus(Parcelas p)
        {
            try
            {
                //con.open();
                String sql = "UPDATE parcelas SET status=:p1, id_usuario_modificacao=:id_mod " +
                    "WHERE id_parcela=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", p.Status);
                com.Parameters.AddWithValue("id", p.IdParcela);

                com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void delete(int id)
        {
            try
            {
                //con.open();
                String sql = "DELETE FROM parcelas WHERE id_parcela=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.ExecuteNonQuery();

                sql = "UPDATE PARCELAS_HIST SET ID_USUARIO_MODIFICACAO =:ID_MOD, DATA_MODIFICACAO = :DATA WHERE ID_PARCELA=:ID";
                com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.Parameters.AddWithValue("data", DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"));
                com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        internal DataTable getParcelas(int idCliente, int idProcedimento)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = @"SELECT * FROM PARCELAS INNER JOIN 
                                PROCEDIMENTO_CLIENTE USING(ID_PROCEDIMENTO_CLIENTE) INNER JOIN 
                                PROCEDIMENTO USING(ID_PROCEDIMENTO) INNER JOIN CLIENTE USING(ID_CLIENTE) 
                            WHERE (ID_CLIENTE = :idCliente OR 99999999 = :idCliente) AND 
                                  (ID_PROCEDIMENTO = :idProcedimento OR 99999999 = :idProcedimento) order by id_parcela";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("idCliente", idCliente);
                com.Parameters.AddWithValue("idProcedimento", idProcedimento);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        internal DataTable getParcelasProcedimento(int IDPROCEDIMENTO)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = @"SELECT * FROM PARCELAS INNER JOIN 
                                PROCEDIMENTO_CLIENTE USING(ID_PROCEDIMENTO_CLIENTE) INNER JOIN 
                                PROCEDIMENTO USING(ID_PROCEDIMENTO) INNER JOIN CLIENTE USING(ID_CLIENTE) 
                            WHERE ID_PROCEDIMENTO_CLIENTE = :ID ORDER BY DATA_VENCIMENTO";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("ID", IDPROCEDIMENTO);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        internal string getParcelasPagas(int id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = @"select count(*) as parcelas from parcelas where id_procedimento_cliente = :id and status IN ('PAGO', 'PROMOCAO', 'PAGO CLINICA')";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            if (dt.Rows.Count == 0 || dt.Rows[0]["parcelas"].ToString().Equals("0"))
                return "0";
            else
                return dt.Rows[0]["parcelas"].ToString();
        }

        public string get(string id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "select status from parcelas where id_parcela = :id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt.Rows[0]["status"].ToString();
        }

        internal void updateValor(int id, float novoValor)
        {
            try
            {
                //con.open();
                String sql = "update parcelas set valor = :valor, id_usuario_modificacao=:id_mod WHERE id_parcela=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.Parameters.AddWithValue("valor", novoValor);

                com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        internal DataTable getParcelasPagasPeriodo(string datainicial, string datafinal, string clinica, string paciente)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = string.Format(@"
                                SELECT  CLIENTE.NOME AS ""Paciente"",
	                                ENDERECO.CIDADE AS ""Clínica"",
	                                'R$' || CAST(CAST(PARCELAS.VALOR AS NUMERIC(12,2)) AS VARCHAR) AS ""Valor"",
	                                CAST(PAGAMENTO_PARCELA.DATA AS VARCHAR) AS ""Vencimento"",
	                                PROCEDIMENTO.NOME_PROCEDIMENTO AS ""Procedimento""
                                FROM CLIENTE INNER JOIN PROCEDIMENTO_CLIENTE USING(ID_CLIENTE)
	                                     INNER JOIN PROCEDIMENTO USING(ID_PROCEDIMENTO)
	                                     INNER JOIN CLINICA USING(ID_CLINICA)
	                                     INNER JOIN ENDERECO ON CLINICA.ID_ENDERECO = ENDERECO.ID_ENDERECO
	                                     INNER JOIN PARCELAS USING(ID_PROCEDIMENTO_CLIENTE)
                                         INNER JOIN PAGAMENTO_PARCELA USING(ID_PARCELA)
                                WHERE   PAGAMENTO_PARCELA.DATA BETWEEN '{0}' AND '{1}' AND
	                                (ID_CLINICA = {3} OR -999 = {3}) AND
	                                (ID_CLIENTE = {2} OR -999 = {2}) AND 
                                    PARCELAS.STATUS LIKE '%PAGO%'
                                ORDER BY CLIENTE.NOME, DATA_VENCIMENTO",
                                            Convert.ToDateTime(datainicial).ToString("MM/dd/yyyy"),
                                            Convert.ToDateTime(datafinal).ToString("MM/dd/yyyy"),
                                            paciente,
                                            clinica);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);

                foreach (DataRow dr in dt.Rows)
                    dr["VENCIMENTO"] = Convert.ToDateTime(dr["VENCIMENTO"]).ToString("dd/MM/yyyy");
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        internal DataTable getParcelasPagasClinicaPeriodo(string datainicial, string datafinal, string clinica, string paciente)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = string.Format(@"
                                SELECT  CLIENTE.NOME AS ""Paciente"",
	                                ENDERECO.CIDADE AS ""Clínica"",
	                                'R$' || CAST(CAST(PARCELAS.VALOR AS NUMERIC(12,2)) AS VARCHAR) AS ""Valor"",
	                                CAST(PAGAMENTO_PARCELA.DATA AS VARCHAR) AS ""Vencimento"",
	                                PROCEDIMENTO.NOME_PROCEDIMENTO AS ""Procedimento""
                                FROM CLIENTE INNER JOIN PROCEDIMENTO_CLIENTE USING(ID_CLIENTE)
	                                     INNER JOIN PROCEDIMENTO USING(ID_PROCEDIMENTO)
	                                     INNER JOIN CLINICA USING(ID_CLINICA)
	                                     INNER JOIN ENDERECO ON CLINICA.ID_ENDERECO = ENDERECO.ID_ENDERECO
	                                     INNER JOIN PARCELAS USING(ID_PROCEDIMENTO_CLIENTE)
                                         INNER JOIN PAGAMENTO_PARCELA USING(ID_PARCELA)
                                WHERE   PAGAMENTO_PARCELA.DATA BETWEEN '{0}' AND '{1}' AND
	                                (ID_CLINICA = {3} OR -999 = {3}) AND
	                                (ID_CLIENTE = {2} OR -999 = {2}) AND 
                                    PARCELAS.STATUS = 'PAGO CLINICA'
                                ORDER BY CLIENTE.NOME, DATA_VENCIMENTO",
                                            Convert.ToDateTime(datainicial).ToString("MM/dd/yyyy"),
                                            Convert.ToDateTime(datafinal).ToString("MM/dd/yyyy"),
                                            paciente,
                                            clinica);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);

                foreach (DataRow dr in dt.Rows)
                    dr["VENCIMENTO"] = Convert.ToDateTime(dr["VENCIMENTO"]).ToString("dd/MM/yyyy");
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        internal DataTable getParcelasPromocaoPeriodo(string datainicial, string datafinal, string clinica, string paciente)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = string.Format(@"
                                SELECT  CLIENTE.NOME AS ""Paciente"",
	                                ENDERECO.CIDADE AS ""Clínica"",
	                                'R$' || CAST(CAST(PARCELAS.VALOR AS NUMERIC(12,2)) AS VARCHAR) AS ""Valor"",
	                                CAST(PARCELAS.DATA_VENCIMENTO AS VARCHAR) AS ""Vencimento"",
	                                PROCEDIMENTO.NOME_PROCEDIMENTO AS ""Procedimento""
                                FROM CLIENTE INNER JOIN PROCEDIMENTO_CLIENTE USING(ID_CLIENTE)
	                                     INNER JOIN PROCEDIMENTO USING(ID_PROCEDIMENTO)
	                                     INNER JOIN CLINICA USING(ID_CLINICA)
	                                     INNER JOIN ENDERECO ON CLINICA.ID_ENDERECO = ENDERECO.ID_ENDERECO
	                                     INNER JOIN PARCELAS USING(ID_PROCEDIMENTO_CLIENTE)
                                WHERE   DATA_VENCIMENTO BETWEEN '{0}' AND '{1}' AND
	                                (ID_CLINICA = {3} OR -999 = {3}) AND
	                                (ID_CLIENTE = {2} OR -999 = {2}) AND 
                                    PARCELAS.STATUS = 'PROMOCAO'
                                ORDER BY CLIENTE.NOME, DATA_VENCIMENTO",
                                            Convert.ToDateTime(datainicial).ToString("MM/dd/yyyy"),
                                            Convert.ToDateTime(datafinal).ToString("MM/dd/yyyy"),
                                            paciente,
                                            clinica);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);

                foreach (DataRow dr in dt.Rows)
                    dr["VENCIMENTO"] = Convert.ToDateTime(dr["VENCIMENTO"]).ToString("dd/MM/yyyy");
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        internal DataTable getParcelasAtrasadasPeriodo(string datainicial, string datafinal, string clinica, string paciente)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = string.Format(@"
                                SELECT  CLIENTE.NOME AS ""Paciente"",
	                                ENDERECO.CIDADE AS ""Clínica"",
	                                'R$' || CAST(CAST(PARCELAS.VALOR AS NUMERIC(12,2)) AS VARCHAR) AS ""Valor"",
	                                CAST(PARCELAS.DATA_VENCIMENTO AS VARCHAR) AS ""Vencimento"",
	                                PROCEDIMENTO.NOME_PROCEDIMENTO AS ""Procedimento""
                                FROM CLIENTE INNER JOIN PROCEDIMENTO_CLIENTE USING(ID_CLIENTE)
	                                     INNER JOIN PROCEDIMENTO USING(ID_PROCEDIMENTO)
	                                     INNER JOIN CLINICA USING(ID_CLINICA)
	                                     INNER JOIN ENDERECO ON CLINICA.ID_ENDERECO = ENDERECO.ID_ENDERECO
	                                     INNER JOIN PARCELAS USING(ID_PROCEDIMENTO_CLIENTE)
                                WHERE   DATA_VENCIMENTO BETWEEN '{0}' AND '{1}' AND
	                                (ID_CLINICA = {3} OR -999 = {3}) AND
	                                (ID_CLIENTE = {2} OR -999 = {2}) AND 
                                    PARCELAS.STATUS = 'ATRASADA'
                                ORDER BY CLIENTE.NOME, DATA_VENCIMENTO",
                                            Convert.ToDateTime(datainicial).ToString("MM/dd/yyyy"),
                                            Convert.ToDateTime(datafinal).ToString("MM/dd/yyyy"),
                                            paciente,
                                            clinica);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);

                foreach (DataRow dr in dt.Rows)
                    dr["VENCIMENTO"] = Convert.ToDateTime(dr["VENCIMENTO"]).ToString("dd/MM/yyyy");
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        internal DataTable getParcelasTransferidasPeriodo(string datainicial, string datafinal, string clinica, string paciente)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = string.Format(@"
                                SELECT  CLIENTE.NOME AS ""Paciente"",
	                                ENDERECO.CIDADE AS ""Clínica"",
	                                'R$' || CAST(CAST(PARCELAS.VALOR AS NUMERIC(12,2)) AS VARCHAR) AS ""Valor"",
	                                CAST(PARCELAS.DATA_VENCIMENTO AS VARCHAR) AS ""Vencimento"",
	                                PROCEDIMENTO.NOME_PROCEDIMENTO AS ""Procedimento""
                                FROM CLIENTE INNER JOIN PROCEDIMENTO_CLIENTE USING(ID_CLIENTE)
	                                     INNER JOIN PROCEDIMENTO USING(ID_PROCEDIMENTO)
	                                     INNER JOIN CLINICA USING(ID_CLINICA)
	                                     INNER JOIN ENDERECO ON CLINICA.ID_ENDERECO = ENDERECO.ID_ENDERECO
	                                     INNER JOIN PARCELAS USING(ID_PROCEDIMENTO_CLIENTE)
                                WHERE   DATA_VENCIMENTO BETWEEN '{0}' AND '{1}' AND
	                                (ID_CLINICA = {3} OR -999 = {3}) AND
	                                (ID_CLIENTE = {2} OR -999 = {2}) AND 
                                    PARCELAS.STATUS = 'TRANSFERIDA'
                                ORDER BY CLIENTE.NOME, DATA_VENCIMENTO",
                                            Convert.ToDateTime(datainicial).ToString("MM/dd/yyyy"),
                                            Convert.ToDateTime(datafinal).ToString("MM/dd/yyyy"),
                                            paciente,
                                            clinica);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);

                foreach (DataRow dr in dt.Rows)
                    dr["VENCIMENTO"] = Convert.ToDateTime(dr["VENCIMENTO"]).ToString("dd/MM/yyyy");
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        internal DataTable getParcelasVencer(string datainicial, string datafinal, string clinica, string paciente)
        {
            DataTable dt = new DataTable();
            try
            {
                /*
                 string sql = string.Format(@"
                                SELECT  CLIENTE.NOME AS ""Paciente"",
	                                ENDERECO.CIDADE AS ""Clínica"",
	                                'R$' || CAST(CAST(PARCELAS.VALOR AS NUMERIC(12,2)) AS VARCHAR) AS ""Valor"",
	                                CAST(PARCELAS.DATA_VENCIMENTO AS VARCHAR) AS ""Vencimento"",
	                                PROCEDIMENTO.NOME_PROCEDIMENTO AS ""Procedimento""
                                FROM CLIENTE INNER JOIN PROCEDIMENTO_CLIENTE USING(ID_CLIENTE)
	                                     INNER JOIN PROCEDIMENTO USING(ID_PROCEDIMENTO)
	                                     INNER JOIN CLINICA USING(ID_CLINICA)
	                                     INNER JOIN ENDERECO ON CLINICA.ID_ENDERECO = ENDERECO.ID_ENDERECO
	                                     INNER JOIN PARCELAS USING(ID_PROCEDIMENTO_CLIENTE)
                                WHERE   DATA_VENCIMENTO BETWEEN '{0}' AND '{1}' AND
	                                (ID_CLINICA = {3} OR -999 = {3}) AND
	                                (ID_CLIENTE = {2} OR -999 = {2}) AND 
                                    PARCELAS.STATUS = 'PROMOCAO'
                                ORDER BY CLIENTE.NOME, DATA_VENCIMENTO",
                                            Convert.ToDateTime(datainicial).ToString("MM/dd/yyyy"),
                                            Convert.ToDateTime(datafinal).ToString("MM/dd/yyyy"),
                                            paciente,
                                            clinica);
                 */
                string sql = "";
                if (!clinica.Contains('-'))
                {
                    sql = string.Format(@"
                                SELECT p.id_parcela as ""Parcela"", 'R$' || CAST(CAST(p.valor AS NUMERIC(12,2)) AS VARCHAR) as ""Valor"", 
                                CAST(p.data_vencimento AS VARCHAR) as ""Vencimento"", c.nome as ""Nome"" 
                                FROM PARCELAS AS P INNER JOIN PROCEDIMENTO_CLIENTE AS PC USING (id_procedimento_cliente) 
                                INNER JOIN CLIENTE AS C USING(id_cliente) INNER JOIN CLINICA USING (id_clinica)
                                WHERE STATUS = 'EM DIA' AND ID_CLINICA = " + clinica + " AND DATA_VENCIMENTO BETWEEN '{0}' AND '{1}' AND (ID_CLIENTE = {2} OR -999 = {2}) ORDER BY data_vencimento limit 100",
                                                Convert.ToDateTime(datainicial).ToString("MM/dd/yyyy"),
                                                Convert.ToDateTime(datafinal).ToString("MM/dd/yyyy"),
                                                paciente,
                                                clinica);
                }
                else
                    sql = string.Format(@"
                                SELECT p.id_parcela as ""Parcela"", 'R$' || CAST(CAST(p.valor AS NUMERIC(12,2)) AS VARCHAR) as ""Valor"", 
                                CAST(p.data_vencimento AS VARCHAR) as ""Vencimento"", c.nome as ""Nome"", endereco.cidade as ""Cidade""
                                FROM PARCELAS AS P INNER JOIN PROCEDIMENTO_CLIENTE AS PC USING (id_procedimento_cliente) 
                                INNER JOIN CLIENTE AS C USING(id_cliente) INNER JOIN CLINICA USING (id_clinica)
                                INNER JOIN ENDERECO ON (clinica.id_endereco = endereco.id_endereco)
                                WHERE STATUS = 'EM DIA' AND DATA_VENCIMENTO BETWEEN '{0}' AND '{1}' AND (ID_CLIENTE = {2} OR -999 = {2}) ORDER BY data_vencimento limit 100",
                                                Convert.ToDateTime(datainicial).ToString("MM/dd/yyyy"),
                                                Convert.ToDateTime(datafinal).ToString("MM/dd/yyyy"),
                                                paciente,
                                                clinica);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);

                foreach (DataRow dr in dt.Rows)
                    dr["VENCIMENTO"] = Convert.ToDateTime(dr["VENCIMENTO"]).ToString("dd/MM/yyyy");
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public double getValor(int id_parcela)
        {
            DataTable dt = new DataTable();
            try
            {
                String sql = "SELECT valor FROM PARCELAS where id_parcela = :id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id_parcela);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return (double)dt.Rows[0]["valor"];
        }

        public string getProcedimentoFromParcela(string id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "select id_procedimento_cliente from parcelas where id_parcela = :id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt.Rows[0]["id_procedimento_cliente"].ToString();
        }

        public void Log(string operacao, string log)
        {
            DateTime dt = DateTime.Now;
            try
            {
                String sql = @"INSERT INTO parcelas_log(operacao, log, data) values (:p1, :p2, :p3)";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", operacao);
                com.Parameters.AddWithValue("p2", log);
                com.Parameters.AddWithValue("p3", DateTime.Now.ToShortDateString() + " - " + DateTime.Now.ToShortTimeString());
                com.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
